//Copyright (C) Microsoft Corporation.  All rights reserved.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Metadata.Edm;
using System.Data.Common;
using System.Data.Objects;
using System.Data.Linq;
using System.Data.EntityClient;
using System.Diagnostics;
using System.Linq;
using SampleSupport;
using System.Xml.Linq;
using System.Text;
using System.IO;
using EntityNorthwind;
using System.Windows.Forms;
using System.Data.SqlClient;


// version erickt1

namespace LINQToEntitiesSampleQueries
{
    [Title("101 LINQ to Entities Samples")]
    [Prefix("LinqToEntities")]
    class LinqToEntitiesSamples : SampleHarness
    {

        private readonly static string dbPath = Path.GetFullPath(Path.Combine(Application.StartupPath, @"..\..\Data\NORTHWND.MDF"));
        
        private readonly static string sqlServerInstance = @".\SQLEXPRESS";

        private readonly static string sqlConnString = "Server=\"" + sqlServerInstance + "\";" + "AttachDBFileName=\"" + dbPath + "\";user instance=true;Integrated Security=SSPI;";
        static string connstr = @"Metadata=.\LINQ to Entities;Provider=System.Data.SqlClient;Provider Connection String='" + sqlConnString + "'";

        EntityNorthwindContext northwindContext;

        public LinqToEntitiesSamples()
        {
            northwindContext = new EntityNorthwindContext(connstr);
        }

        #region Restriction Operators
        [Category("Restriction Operators")]
        [Title("Where - Simple 1")]
        [Description("This sample uses WHERE to find all customers in Seattle.")]
        public void LinqToEntities1()
        {

            var query = from cust in northwindContext.Customers
                        where cust.City == "Seattle"
                        select cust;

            ObjectDumper.Write(query);
        }


        [Category("Restriction Operators")]
        [Title("Where - Simple 2")]
        [Description("This sample uses WHERE to find all orders placed in 1994.")]
        public void LinqToEntities2()
        {
            DateTime dt = new DateTime(1994, 1, 1);
            var query = from order in northwindContext.Orders
                        where order.OrderDate > dt
                        select order;

            ObjectDumper.Write(query);
        }

        [Category("Restriction Operators")]
        [Title("Where - Simple 3")]
        [Description("This sample uses WHERE to filter for Products that have stock below their reorder level and have a units on order of zero.")]
        public void LinqToEntities3()
        {
            var query = from p in northwindContext.Products
                        where p.UnitsInStock < p.ReorderLevel && p.UnitsOnOrder == 0
                        select p;

            ObjectDumper.Write(query);
        }


        [Category("Restriction Operators")]
        [Title("Where - Simple 4")]
        [Description("This sample uses WHERE to filter out Products that have a UnitPrice less than 10.")]
        public void LinqToEntities4()
        {
            var query = from p in northwindContext.Products
                        where p.UnitPrice < 10
                        select p;

            ObjectDumper.Write(query);
        }

        [Category("Restriction Operators")]
        [Title("Where - Related Entities 1")]
        [Description("This sample uses WHERE to get orders for Customers in Mexico.")]
        public void LinqToEntities5()
        {
            var query = from o in northwindContext.Orders
                        where o.Customer.Country == "Mexico"
                        select o;

            ObjectDumper.Write(query);
        }

        [Category("Restriction Operators")]
        [Title("Where - Related Entities 2")]
        [Description("This sample uses WHERE to get orders sold by employees in the UK.")]
        public void LinqToEntities6()
        {
            var query = from o in northwindContext.Orders
                        where o.Employee.Country == "UK"
                        select o;

            ObjectDumper.Write(query);
        }

        [Category("Restriction Operators")]
        [Title("Any - 1")]
        [Description("This sample uses ANY to get employees have sold an order.")]
        public void LinqToEntities7()
        {
            var query = from e in northwindContext.Employees
                        where e.Orders.Any(o => o != null)
                        select e;

            ObjectDumper.Write(query);
        }

        [Category("Restriction Operators")]
        [Title("Any - 2")]
        [Description("This sample uses ANY to check for any out-of-stock products.")]
        public void LinqToEntities8()
        {
            var query = northwindContext
                .Suppliers
                .Where(s => s.Product
                           .Any(p => p.UnitsInStock == 0))
                .Select(s => s);

            ObjectDumper.Write(query);
        }


        [Category("Restriction Operators")]
        [Title("Any - Related Entities")]
        [Description("This sample uses WHERE and ANY to get employees who sold an order to any customer in Mexico.")]
        public void LinqToEntities9()
        {
            var query = from e in northwindContext.Employees
                        where e.Orders.Any(o => o.Customer.Country == "Mexico")
                        select e;

            ObjectDumper.Write(query);
        }

        [Category("Restriction Operators")]
        [Title("All - Simple")]
        [Description("This sample uses ALL to get employees who sold orders only to customers not in Canada.")]
        public void LinqToEntities10()
        {
            var query = from e in northwindContext.Employees
                        where e.Orders.All(o => o.Customer.Country != "Canada")
                        select e;

            ObjectDumper.Write(query);
        }

        #endregion

        #region Projection Operators

        [Category("Projection Operators")]
        [Title("Select - Simple 1")]
        [Description("This samples uses SELECT to get all Customers as Entity Objects.")]
        public void LinqToEntities11()
        {
            var query = from c in northwindContext.Customers
                        select c;

            ObjectDumper.Write(query);
        }

        [Category("Projection Operators")]
        [Title("Select - Simple 2")]
        [Description("This samples uses SELECT to get all Customer Contact Names as Strings.")]
        public void LinqToEntities12()
        {
            var query = from c in northwindContext.Customers
                        select c.ContactName;

            ObjectDumper.Write(query);
        }

        [Category("Projection Operators")]
        [Title("Select - Anonymous 1")]
        [Description("This samples uses SELECT to get all Customer Contact Names as an anonoymous type.")]
        public void LinqToEntities13()
        {
            var query = from c in northwindContext.Customers
                        select new { c.ContactName };

            ObjectDumper.Write(query);
        }


        [Category("Projection Operators")]
        [Title("Select - Anonymous 2")]
        [Description("This sample uses SELECT to get all Orders as anonymous type")]
        public void LinqToEntities14()
        {
            var query = from o in northwindContext.Orders
                        select new { o };

            ObjectDumper.Write(query, 1);
        }

        [Category("Projection Operators")]
        [Title("Select - Anonymous 3")]
        [Description("This sample uses SELECT to get all Orders and associated Employees as anonymous type")]
        public void LinqToEntities15()
        {
            var query = from o in northwindContext.Orders
                        select new { o, o.Employee };

            ObjectDumper.Write(query, 1);
        }

        [Category("Projection Operators")]
        [Title("Select - Nested Collection ")]
        [Description("This sample uses SELECT to get all Customers, and those Orders for each customer with a freight > 5")]
        public void LinqToEntities15a()
        {
            var query = northwindContext.Customers.Select(c => new { Customer = c, Orders = c.Orders.Where(o => o.Freight > 5) });

            ObjectDumper.Write(query, 1);
        }


        [Category("Projection Operators")]
        [Title("SelectMany - Simple 1")]
        [Description("This sample uses SELECTMANY to get all Orders for a Customer as a flat result")]
        public void LinqToEntities16()
        {
            var query = from c in northwindContext.Customers
                        where c.CustomerID == "ALFKI"
                        from o in c.Orders
                        select o;

            ObjectDumper.Write(query);
        }

        [Category("Projection Operators")]
        [Title("SelectMany - Simple 2")]
        [Description("This sample uses SELECTMANY to get all Orders for a Customer as a flat result using LINQ operators")]
        public void LinqToEntities17()
        {
            var query = northwindContext.Customers.Where(cust => cust.CustomerID == "ALFKI")
                .SelectMany(cust => cust.Orders);

            ObjectDumper.Write(query);
        }

        [Category("Projection Operators")]
        [Title("SelectMany - Simple 3")]
        [Description("This sample uses SELECTMANY to get all Orders for Customers in Denmark as a flat result")]
        public void LinqToEntities18()
        {
            var query = from c in northwindContext.Customers
                        where c.Country == "Denmark"
                        from o in c.Orders
                        select o;

            ObjectDumper.Write(query);
        }

        [Category("Projection Operators")]
        [Title("SelectMany - Simple 4")]
        [Description("This sample uses SELECTMANY to get all Orders for Customers in Denmark as a flat result using LINQ operators")]
        public void LinqToEntities19()
        {
            var query = northwindContext.Customers.Where(cust => cust.Country == "Denmark")
                .SelectMany(cust => cust.Orders);

            ObjectDumper.Write(query);
        }


        [Category("Projection Operators")]
        [Title("SelectMany - Predicate 1")]
        [Description("This sample uses SELECTMANY to get all Orders for Customers in Denmark as a flat result")]
        public void LinqToEntities20()
        {
            var query = from c in northwindContext.Customers
                        where c.Country == "Denmark"
                        from o in c.Orders
                        where o.Freight > 5
                        select o;

            ObjectDumper.Write(query);
        }

        [Category("Projection Operators")]
        [Title("SelectMany - Predicate 2")]
        [Description("This sample uses SELECTMANY to get all Orders for Customers in Denmark as an anonymous type containing the Orders and Customer flat result")]
        public void LinqToEntities21()
        {
            var query = from c in northwindContext.Customers
                        where c.Country == "Denmark"
                        from o in c.Orders
                        where o.Freight > 5
                        select new { c, o };

            ObjectDumper.Write(query);
        }

        [Category("Projection Operators")]
        [Title("SelectMany - Predicate 3")]
        [Description("This sample uses SELECTMANY to get all Orders for Customers in Denmark as a flat result using LINQ opeartors")]
        public void LinqToEntities22()
        {
            var query = northwindContext.Customers.Where(cust => cust.Country == "Denmark")
                .SelectMany(cust => cust.Orders.Where(o => o.Freight > 5));

            ObjectDumper.Write(query);
        }

        #endregion

        #region Aggregate Operators

        [Category("Aggregate Operators")]
        [Title("Count - Simple")]
        [Description("This sample uses COUNT to get the number of Orders.")]
        public void LinqToEntities23()
        {
            var query = northwindContext.Orders.Count();

            ObjectDumper.Write(query);
        }


        [Category("Aggregate Operators")]
        [Title("Count - Predicate 1")]
        [Description("This sample uses COUNT to get the number of Orders placed by Customers in Mexico.")]
        public void LinqToEntities24()
        {
            var query = northwindContext.Orders.Where(o => o.Customer.Country == "Mexico").Count();

            ObjectDumper.Write(query);
        }

        [Category("Aggregate Operators")]
        [Title("Count - Predicate 2")]
        [Description("This sample uses COUNT to get the number of Orders shipped to Mexico.")]
        public void LinqToEntities25()
        {
            var query = northwindContext.Orders
                .Where(o => o.ShipCountry == "Mexico").Count();

            ObjectDumper.Write(query);
        }

        [Category("Aggregate Operators")]
        [Title("Sum - Simple 1")]
        [Description("This sample uses SUM to find the total freight over all Orders.")]
        public void LinqToEntities26()
        {
            var query = northwindContext.Orders.Select(o => o.Freight).Sum();

            ObjectDumper.Write(query);
        }

        [Category("Aggregate Operators")]
        [Title("Sum - Simple 2")]
        [Description("This sample uses SUM to find the total number of units on order over all Products.")]
        public void LinqToEntities27()
        {
            var query = northwindContext.Products.Sum(p => p.UnitsOnOrder);

            ObjectDumper.Write(query);
        }

        [Category("Aggregate Operators")]
        [Title("Sum - Simple 3")]
        [Description("This sample uses SUM to find the total number of units on order over all Products out-of-stock.")]
        public void LinqToEntities28()
        {
            var query = northwindContext.Products.Where(p => p.UnitsInStock == 0).Sum(p => p.UnitsOnOrder);

            ObjectDumper.Write(query);
        }


        [Category("Aggregate Operators")]
        [Title("Min - Simple 1")]
        [Description("This sample uses MIN to find the lowest unit price of any Product.")]
        public void LinqToEntities29()
        {
            var query = northwindContext.Products.Select(p => p.UnitPrice).Min();

            ObjectDumper.Write(query);
        }

        [Category("Aggregate Operators")]
        [Title("Min - Simple 2")]
        [Description("This sample uses MIN to find the lowest freight of any Order.")]
        public void LinqToEntities30()
        {
            var query = northwindContext.Orders.Min(o => o.Freight);

            ObjectDumper.Write(query);
        }

        [Category("Aggregate Operators")]
        [Title("Min - Predicate")]
        [Description("This sample uses MIN to find the lowest freight of any Order shipped to Mexico.")]
        public void LinqToEntities31()
        {
            var query = northwindContext.Orders.Where(o => o.ShipCountry == "Mexico").Min(o => o.Freight);

            ObjectDumper.Write(query);
        }


        [Category("Aggregate Operators")]
        [Title("Min - Grouping")]
        [Description("This sample uses Min to find the Products that have the lowest unit price in each category, and returns the result as an anonoymous type.")]
        public void LinqToEntities32()
        {

            var query = from p in northwindContext.Products
                        group p by p.Category into g
                        select new
                               {
                                   CategoryID = g.Key,
                                   CheapestProducts =
                                       from p2 in g
                                       where p2.UnitPrice == g.Min(p3 => p3.UnitPrice)
                                       select p2
                               };

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Max - Simple 1")]
        [Description("This sample uses MAX to find the latest hire date of any Employee.")]
        public void LinqToEntities33()
        {
            var query = northwindContext.Employees.Select(e => e.HireDate).Max();

            ObjectDumper.Write(query);
        }

        [Category("Aggregate Operators")]
        [Title("Max - Simple 2")]
        [Description("This sample uses MAX to find the most units in stock of any Product.")]
        public void LinqToEntities34()
        {
            var query = northwindContext.Products.Max(p => p.UnitsInStock);

            ObjectDumper.Write(query);
        }

        [Category("Aggregate Operators")]
        [Title("Max - Predicate")]
        [Description("This sample uses MAX to find the most units in stock of any Product with CategoryID = 1.")]
        public void LinqToEntities35()
        {
            var query = northwindContext.Products.Where(p => p.Category.CategoryID == 1).Max(p => p.UnitsInStock);
            ObjectDumper.Write(query);
        }

        [Category("Aggregate Operators")]
        [Title("Max - Grouping")]
        [Description("This sample uses MAX to find the Products that have the highest unit price in each category, and returns the result as an anonoymous type.")]
        public void LinqToEntities36()
        {
            var query = from p in northwindContext.Products
                        group p by p.Category into g
                        select new
                               {
                                   g.Key,
                                   MostExpensiveProducts =
                                       from p2 in g
                                       where p2.UnitPrice == g.Max(p3 => p3.UnitPrice)
                                       select p2
                               };

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Average - Simple 1")]
        [Description("This sample uses AVERAGE to find the average freight of all Orders.")]
        public void LinqToEntities37()
        {
            var query = northwindContext.Orders.Select(o => o.Freight).Average();

            ObjectDumper.Write(query);
        }

        [Category("Aggregate Operators")]
        [Title("Average - Simple 2")]
        [Description("This sample uses AVERAGE to find the average unit price of all Products.")]
        public void LinqToEntities38()
        {
            var query = northwindContext.Products.Average(p => p.UnitPrice);

            ObjectDumper.Write(query);
        }

        [Category("Aggregate Operators")]
        [Title("Average - Predicate")]
        [Description("This sample uses AVERAGE to find the average unit price of all Products with CategoryID = 1.")]
        public void LinqToEntities39()
        {
            var query = northwindContext.Products.Where(p => p.Category.CategoryID == 1).Average(p => p.UnitPrice);

            ObjectDumper.Write(query);
        }

        [Category("Aggregate Operators")]
        [Title("Average - Grouping 1")]
        [Description("This sample uses AVERAGE to find the Products that have unit price higher than the average unit price of the category for each category.")]
        public void LinqToEntities40()
        {
            var query = from p in northwindContext.Products
                        group p by p.Category into g
                        select new
                               {
                                   g.Key,
                                   ExpensiveProducts =
                                       from p2 in g
                                       where p2.UnitPrice > g.Average(p3 => p3.UnitPrice)
                                       select p2
                               };

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Average - Grouping 2")]
        [Description("This sample uses AVERAGE to find the average unit price of each category.")]
        public void LinqToEntities41()
        {
            var query = from p in northwindContext.Products
                        group p by p.Category into g
                        select new
                               {
                                   g.Key,
                                   Average = g.Average(p => p.UnitPrice)
                               };

            ObjectDumper.Write(query, 1);
        }

        #endregion

        #region Set Operators

        [Category("Set And Element Operators")]
        [Title("First - Simple")]
        [Description("This sample uses FIRST and WHERE to get the first (database order) order that is shipped to Seattle. The WHERE predicate is evaluated on the server.")]
        public void LinqToEntities42()
        {
            var query = from o in northwindContext.Orders
                        where o.ShipCity == "Seattle"
                        select o;

            // Feb CTP requires AsEnumerable()
            var result = query.AsEnumerable().First();

            ObjectDumper.Write(result);
        }

        [Category("Set And Element Operators")]
        [Title("First - Predicate")]
        [Description("This sample uses FIRST to get the first (database order) order that is shipped to Seattle. The predicate is evaluated on the client.")]
        public void LinqToEntities43()
        {
            var query = from o in northwindContext.Orders
                        select o;

            // Feb CTP requires AsEnumerable()
            var result = query
                .AsEnumerable()
                .First(x => x.ShipCity == "Seattle");

            ObjectDumper.Write(result);
        }

        [Category("Set And Element Operators")]
        [Title("First - Ordered")]
        [Description("This sample uses FIRST, WHERE and ORDER BY to get the first order that is shipped to Seattle, ordered by date. The predicate is evaluated on the server.")]
        public void LinqToEntities44()
        {
            var query = from o in northwindContext.Orders
                        where o.ShipCity == "Seattle"
                        orderby o.OrderDate
                        select o;

            // Feb CTP requires AsEnumerable()
            var result = query.AsEnumerable().First();

            ObjectDumper.Write(result);
        }


        [Category("Set And Element Operators")]
        [Title("Distinct - Simple")]
        [Description("This sample uses DISTINCT to get all the categories of products.")]
        public void LinqToEntities45()
        {
            var query = northwindContext.Products.Select(o => o.Category).Distinct();

            ObjectDumper.Write(query);
        }

        [Category("Set And Element Operators")]
        [Title("Union - Simple")]
        [Description("This sample uses UNION to get all the orders where the shipping country was Mexico or Canada.")]
        public void LinqToEntities46()
        {
            var mexico = northwindContext.Orders.Where(o => o.ShipCountry == "Mexico").Select(o => o);
            var canada = northwindContext.Orders.Where(o => o.ShipCountry == "Canada").Select(o => o);
            var query = mexico.Union(canada);

            ObjectDumper.Write(query);
        }

        [Category("Set And Element Operators")]
        [Title("Union - With Distinct")]
        [Description("This sample uses UNION and DISTINCT to get all the employees from orders where the shipping country was Mexico or Canada.")]
        public void LinqToEntities47()
        {
            var mexico = northwindContext.Orders.Where(o => o.ShipCountry == "Mexico").Select(o => o);
            var canada = northwindContext.Orders.Where(o => o.ShipCountry == "Canada").Select(o => o);
            var union = mexico.Union(canada).Select(o => o.Employee);

            var query = union.Distinct();

            ObjectDumper.Write(query);
        }

        [Category("Set And Element Operators")]
        [Title("Concat - Simple")]
        [Description("This sample uses CONCAT to get all orders where the shipping country was Mexico or Canada.")]
        public void LinqToEntities48()
        {
            var mexico = northwindContext.Orders.Where(o => o.ShipCountry == "Mexico").Select(o => o);
            var canada = northwindContext.Orders.Where(o => o.ShipCountry == "Canada").Select(o => o);

            var query = mexico.Concat(canada);

            ObjectDumper.Write(query);
        }

        [Category("Set And Element Operators")]
        [Title("Intersect - Simple 1")]
        [Description("This sample uses INTERSECT to get common employees where an order was shipped to Mexico or Canada.")]
        public void LinqToEntities49()
        {
            var mexico = northwindContext.Orders.Where(o => o.ShipCountry == "Mexico").Select(o => o.Employee);
            var canada = northwindContext.Orders.Where(o => o.ShipCountry == "Canada").Select(o => o.Employee);

            var query = mexico.Intersect(canada);

            ObjectDumper.Write(query);
        }

        [Category("Set And Element Operators")]
        [Title("Intersect - Simple 2")]
        [Description("This sample uses INTERSECT to get common employees where an order was shipped to Mexico or Canada in one consolidated query.")]
        public void LinqToEntities50()
        {
            var query = northwindContext.Orders.Where(o => o.ShipCountry == "Mexico").Select(o => o.Employee).Intersect(northwindContext.Orders.Where(o => o.ShipCountry == "Canada").Select(o => o.Employee));

            ObjectDumper.Write(query);
        }

        [Category("Set And Element Operators")]
        [Title("Except - Simple 1")]
        [Description("This sample uses EXCEPT to get employees who shipped orders to Mexico but not Canada.")]
        public void LinqToEntities51()
        {
            var query = northwindContext.Orders.Where(o => o.ShipCountry == "Mexico").Select(o => o.Employee).Except(northwindContext.Orders.Where(o => o.ShipCountry == "Canada").Select(o => o.Employee));

            ObjectDumper.Write(query);
        }

        [Category("Set And Element Operators")]
        [Title("Except - Simple 2")]
        [Description("This sample uses EXCEPT to get employees with no orders sent to Mexico.")]
        public void LinqToEntities52()
        {
            var query = northwindContext.Employees.Select(e => e)
                .Except(northwindContext.Orders.Where(o => o.ShipCountry == "Mexico").Select(o => o.Employee));

            ObjectDumper.Write(query);
        }


        #endregion


        #region Ordering and Grouping

        [Category("Ordering and Grouping")]
        [Title("OrderBy - Simple 1")]
        [Description("Select all customers ordered by ContactName.")]
        public void LinqToEntities53()
        {
            var query = from c in northwindContext.Customers
                        orderby c.ContactName
                        select c;

            ObjectDumper.Write(query);
        }

        [Category("Ordering and Grouping")]
        [Title("OrderBy - Simple 2")]
        [Description("Select all customers ordered by ContactName descending.")]
        public void LinqToEntities54()
        {
            var query = from c in northwindContext.Customers
                        orderby c.CompanyName descending
                        select c;

            ObjectDumper.Write(query);
        }

        [Category("Ordering and Grouping")]
        [Title("OrderBy - Simple 3")]
        [Description("Select an anonoymous type with all product IDs ordered by UnitInStock.")]
        public void LinqToEntities55()
        {
            var query = from p in northwindContext.Products
                        orderby p.UnitsInStock
                        select new { p.ProductID };

            ObjectDumper.Write(query);
        }

        [Category("Ordering and Grouping")]
        [Title("OrderBy - Simple 4")]
        [Description("Select an anonoymous type with all product IDs ordered by UnitInStock using LINQ operators.")]
        public void LinqToEntities56()
        {
            var query = northwindContext.Products.OrderBy(p => p.UnitsInStock)
                .Select(p2 => new { p2.ProductID });

            ObjectDumper.Write(query);
        }


        [Category("Ordering and Grouping")]
        [Title("OrderByDescending - Simple 1")]
        [Description("Select all customers ordered by the descending region.")]
        public void LinqToEntities57()
        {
            var query = from c in northwindContext.Customers
                        orderby c.Region descending
                        select c;

            ObjectDumper.Write(query);
        }

        [Category("Ordering and Grouping")]
        [Title("OrderByDescending - Simple 2")]
        [Description("Select all customers ordered by the descending region using LINQ operators.")]
        public void LinqToEntities58()
        {
            var query = northwindContext.Customers.Select(c => c).OrderByDescending(c2 => c2.Region);

            ObjectDumper.Write(query);
        }

        [Category("Ordering and Grouping")]
        [Title("OrderBy with ThenBy")]
        [Description("Select all customers ordered by the region, then the contact name.")]
        public void LinqToEntities59()
        {
            var query = northwindContext.Customers.Select(c => c).OrderBy(c => c.Region).ThenBy(c => c.ContactName);

            ObjectDumper.Write(query);
        }

        [Category("Ordering and Grouping")]
        [Title("OrderByDescending with ThenBy")]
        [Description("Select all customers ordered by the region in descending order, then the contact name.")]
        public void LinqToEntities60()
        {
            var query = northwindContext.Customers.Select(c => c).OrderByDescending(c => c.Region).ThenBy(c => c.ContactName);

            ObjectDumper.Write(query);
        }

        [Category("Ordering and Grouping")]
        [Title("OrderBy with ThenByDescending")]
        [Description("Select all customers ordered by the region then the contact name in descending order.")]
        public void LinqToEntities61()
        {
            var query = northwindContext.Customers.Select(c => c).OrderBy(c => c.Region).ThenByDescending(c => c.ContactName);

            ObjectDumper.Write(query);
        }


        [Category("Ordering and Grouping")]
        [Title("OrderByDescending - Simple 3")]
        [Description("Select all products ordered by the descending unit price.")]
        public void LinqToEntities62()
        {
            var query = from p in northwindContext.Products
                        orderby p.UnitPrice descending
                        select p;

            ObjectDumper.Write(query);
        }


        [Category("Ordering and Grouping")]
        [Title("OrderBy - FK Collection")]
        [Description("Select all orders for a customer ordered by date that the order was placed.")]
        public void LinqToEntities63()
        {
            var query = northwindContext.Customers.Where(cust => cust.CustomerID == "ALFKI")
                .SelectMany(c => c.Orders.Select(o => o))
                .OrderBy(o2 => o2.OrderDate);

            foreach (var order in query)
            {
                ObjectDumper.Write(order);
            }
        }

        [Category("Ordering and Grouping")]
        [Title("Grouping - Simple 1")]
        [Description("Select all Regions with a customer.")]
        public void LinqToEntities64()
        {
            var query = from c in northwindContext.Customers
                        group c by c.Region into regions
                        select new { regions.Key };

            ObjectDumper.Write(query);
        }

        [Category("Ordering and Grouping")]
        [Title("Grouping - Simple 2")]
        [Description("Select all dates with orders placed.")]
        public void LinqToEntities65()
        {
            var query = from o in northwindContext.Orders
                        group o by o.OrderDate into dates
                        select new { dates.Key };

            ObjectDumper.Write(query);
        }

        [Category("Ordering and Grouping")]
        [Title("Grouping - Join 1")]
        [Description("Select all Regions and customer count for each region.")]
        public void LinqToEntities66()
        {
            var query = from c in northwindContext.Customers
                        group c by c.Region into regions
                        select new { Region = regions.Key, Count = regions.Count() };

            ObjectDumper.Write(query);
        }

        [Category("Ordering and Grouping")]
        [Title("Grouping on Key")]
        [Description("Select all Regions and customer count for each region using LINQ operator.")]
        public void LinqToEntities67()
        {
            var query = northwindContext.Customers.GroupBy(c => c.Region).Select(r => new { region = r.Key, count = r.Count() });

            ObjectDumper.Write(query);
        }

        [Category("Ordering and Grouping")]
        [Title("Grouping with a join on Key 1")]
        [Description("Select all Customer Regions with the total Freight on all orders for Customers in that Region.")]
        public void LinqToEntities68()
        {
            var query = from c in northwindContext.Customers
                        group c by c.Region into regions
                        join c2 in northwindContext.Customers on regions.Key equals c2.Region
                        select new { region = regions.Key, total = c2.Orders.Sum(o => o.Freight) };

            ObjectDumper.Write(query);
        }

        [Category("Ordering and Grouping")]
        [Title("Grouping with a Key 2")]
        [Description("Select all Customer Regions with the total Freight on all orders for Customers in that Region using LINQ operators.")]
        public void LinqToEntities69()
        {
            var query = northwindContext.Customers.GroupBy(c => c.Region)
                .Select(g => new
                             {
                                 Region = g.Key, FreightTotal = g
                                .SelectMany(c2 => c2.Orders)
                                .Sum(o => o.Freight)
                             });

            ObjectDumper.Write(query);
        }


        #endregion

        #region Relationship Navigation

        [Category("Relationship Navigation")]
        [Title("Select - FK Collection 1")]
        [Description("Select a sequence of all the orders for a customer using Select.")]
        public void LinqToEntities70()
        {
            var query = northwindContext.Customers.Where(cust => cust.CustomerID == "ALFKI")
                .Select(c => c.Orders.Select(o => o));

            foreach (var order in query)
            {
                ObjectDumper.Write(order);
            }
        }

        [Category("Relationship Navigation")]
        [Title("Select - FK Collection 2")]
        [Description("Select all the orders for a customer using SelectMany.")]
        public void LinqToEntities71()
        {
            var query = northwindContext.Customers.Where(cust => cust.CustomerID == "ALFKI").SelectMany(c => c.Orders);

            ObjectDumper.Write(query);
        }

        [Category("Relationship Navigation")]
        [Title("Select - FK Collection property")]
        [Description("Select all Employee IDs, and the count of the their orders.")]
        public void LinqToEntities72()
        {
            var query = from e in northwindContext.Employees
                        select new { e, orders =  e.Orders.Select(o => o) };

            ObjectDumper.Write(query);
        }

        /* not enabled for Feb CTP
        [Category("Relationship Navigation")]
        [Title("Select - FK Collection property 2")]
        [Description("Select number of orders placed in 2002 for a customer.")]
        public void LinqToEntities74()
        {
            var query = northwindContext.Customers
                .Where(cust => cust.CustomerID == "ALFKI")
                .SelectMany(c => c.Orders)
                .Where(o => o.OrderDate.Year == 2002);

            ObjectDumper.Write(query);
        }
        */

        [Category("Relationship Navigation")]
        [Title("Select - FK Collection Aggregate property")]
        [Description("Select a customer and the sum of the freight of thier orders.")]
        public void LinqToEntities73()
        {
            var query = northwindContext.Customers.Where(cust => cust.CustomerID == "ALFKI")
                .Select(c => c.Orders.Sum(o => o.Freight));

            ObjectDumper.Write(query);
        }

        [Category("Relationship Navigation")]
        [Title("Select - FK collection predicate")]
        [Description("Select customers with an order where the shipping address is the same as the customers.")]
        public void LinqToEntities75()
        {
            var query = northwindContext.Customers.Where(cust => cust.Orders.Any(o => o.ShipAddress == cust.Address)).Select(c2 => c2);

            ObjectDumper.Write(query);
        }

        [Category("Relationship Navigation")]
        [Title("Select - FK collection Grouping")]
        [Description("Selects all regions with a customer, and shows the sum of orders for customers for each region.")]
        public void LinqToEntities76()
        {
            var query = from c in northwindContext.Customers
                        group c by c.Region into regions
                        join c2 in northwindContext.Customers on regions.Key equals c2.Region
                        select new { region = regions.Key, total = c2.Orders.Sum(o => o.Freight) };

            ObjectDumper.Write(query);
        }

        #endregion

        #region Inheritance

        [Category("Table per Hierarchy Inheritance")]
        [Title("One Level Hierarchy - Simple")]
        [Description("Select all products, both active and discontinued products, and shows the type.")]
        public void LinqToEntities77()
        {
            var query = northwindContext
                .Products
                .Select(p => p);

            // we need AsEnumerable to force execution, as GetType is not defined in store
            var query2 = query
                .AsEnumerable()
                .Select(p => new { type = p.GetType().ToString(), prod = p });

            ObjectDumper.Write(query2);
        }


        [Category("Table per Hierarchy Inheritance")]
        [Title("One Level Hierarchy - OfType - Simple 1")]
        [Description("Select only discontinued products.")]
        public void LinqToEntities78()
        {
            var query = northwindContext.Products.OfType<DiscontinuedProduct>().Select(p => p);

            ObjectDumper.Write(query);
        }


        [Category("Table per Hierarchy Inheritance")]
        [Title("One Level Hierarchy - OfType - Simple 2")]
        [Description("Select only products, which will reutrn all Products and subtypes of Products (DiscontinuedProducts and ActiveProducts).")]
        public void LinqToEntities79()
        {
            var query = northwindContext.Products.OfType<Product>().Select(p => p);

            ObjectDumper.Write(query);
        }

        [Category("Table per Hierarchy Inheritance")]
        [Title("One Level Hierarchy - Getting Supertype - OfType")]
        [Description("Select only active products.")]
        public void LinqToEntities80()
        {
            var query = northwindContext.Products.OfType<ActiveProduct>();

            ObjectDumper.Write(query);
        }

        [Category("Table per Hierarchy Inheritance")]
        [Title("One Level Hierarchy - Getting Supertype - Local")]
        [Description("Select only discontinued products.")]
        public void LinqToEntities81()
        {
            var query = northwindContext.Products.Where(p => p is DiscontinuedProduct);

            ObjectDumper.Write(query);
        }


        [Category("Table per Hierarchy Inheritance")]
        [Title("Complex Hierarchy - Simple")]
        [Description("Select all contacts and show the type of each.")]
        public void LinqToEntities82()
        {
            var query = northwindContext
                .Contacts
                .Select(c => c);

            // we need AsEnumerable to force execution, as GetType is not defined in store
            var query2 = query
                .AsEnumerable()
                .Select(c => new { type = c.GetType().ToString() });

            ObjectDumper.Write(query2);
        }

        [Category("Table per Hierarchy Inheritance")]
        [Title("Complex Hierarchy - OfType 1")]
        [Description("Select all Shipper contacts.")]
        public void LinqToEntities83()
        {
            var query = northwindContext
                .Contacts
                .OfType<ShipperContact>()
                .Select(c => c);

            ObjectDumper.Write(query);
        }

        [Category("Table per Hierarchy Inheritance")]
        [Title("Complex Hierarchy - OfType 2")]
        [Description("Select all Full contacts, which includes suppliers, customers, and employees.")]
        public void LinqToEntities84()
        {
            var query = northwindContext
                .Contacts
                .OfType<FullContact>()
                .Select(c => c);

            ObjectDumper.Write(query);
        }

        /* not enabled for Feb CTP
        [Category("Table per Hierarchy Inheritance")]
        [Title("Complex Hierarchy - using supertype")]
        [Description("Select all Customers and Employees, cast as FullContacts to allow join.")]
        public void LinqToEntities85()
        {
            var query = northwindContext
                .Contacts
                .OfType<CustomerContact>()
                .Cast<FullContact>()
                .Union(northwindContext.Contacts.OfType<EmployeeContact>().Cast<FullContact>().Select(ec => ec ))
                .AsEnumerable()
                .Select(c => new {type = c.GetType().ToString(), companyName =  c.CompanyName } );

            ObjectDumper.Write(query);
        }*/

        [Category("Table per Concrete Type Inheritance")]
        [Title("Simple")]
        [Description("Select all federated products and display thier types.")]
        public void LinqToEntities86()
        {
            var query = northwindContext.ProductsFedarated.AsEnumerable().Select(p => new { type = p.GetType().ToString(), p });

            ObjectDumper.Write(query);
        }

        [Category("Table per Concrete Type Inheritance")]
        [Title("OfType")]
        [Description("Select all discontinued federated products.")]
        public void LinqToEntities87()
        {
            var query = northwindContext.ProductsFedarated.OfType<DiscontinuedProductFedarated>().AsEnumerable().Select(p => new { type = p.GetType().ToString(), p });

            ObjectDumper.Write(query);
        }

        [Category("Table per Type Inheritance")]
        [Title("Simple")]
        [Description("Select all contacts and shows their types.")]
        public void LinqToEntities88()
        {
            var query = northwindContext.ContactsSplit.AsEnumerable().Select(c => new { type = c.GetType().ToString(), c });

            ObjectDumper.Write(query);
        }

        [Category("Table per Type Inheritance")]
        [Title("OfType 1")]
        [Description("Select all Customers.")]
        public void LinqToEntities89()
        {
            var query = northwindContext
                .ContactsSplit
                .OfType<CustomerContactSplit>()
                .AsEnumerable()
                .Select(c => new { type = c.GetType().ToString(), c });

            ObjectDumper.Write(query);
        }


        /*
        [Category("Table per Type Inheritance")]
        [Title("OfType 2")]
        [Description("Select all Customers who are also employees, both as the base ContactSplit type (empty set).")]
        public void LinqToEntities90()
        {
            var query = northwindContext
                .ContactsSplit
                .OfType<CustomerContactSplit>()
                .Cast<ContactSplit>()
                .Intersect(northwindContext.ContactsSplit.OfType<EmployeeContactSplit>().Cast<ContactSplit>())
                .AsEnumerable()
                .Select(c => new { type = c.GetType().ToString(), c });

            ObjectDumper.Write(query);
        }
        */
        #endregion

        
        #region Runtime behavior closure

        class MyClass {
            public static decimal Val = 50;

            public decimal GetVal()
            {
                return MyClass.Val;
            }
        }

        [Category("Runtime behavior example")]
        [Title("Static variable reference")]
        [Description("Uses a local variable as a query parameter.")]
        public void LinqToEntities91()
        {
            MyClass c = new MyClass();
            var query = northwindContext.Orders.Where(o => o.Freight > MyClass.Val).Select(o => new { o.Freight, o });

            ObjectDumper.Write(query);
        }

        [Category("Runtime behavior example")]
        [Title("Query Parameters")]
        [Description("Uses a the value of the local variable at query execution time.")]
        public void LinqToEntities92()
        {
            decimal x = 50;

            var query = northwindContext.Orders.Where(o => o.Freight > x).Select(o => new { o.Freight, o });

            x = 100;

            ObjectDumper.Write(query);
        }

        [Category("Runtime behavior example")]
        [Title("Deferred Execution and Query Parameters")]
        [Description("Each execution uses the current value of the local variable.")]
        public void LinqToEntities93()
        {
            decimal x = 100;

            var query = northwindContext.Orders.Where(o => o.Freight > x).Select(o => new { o.Freight, o });

            ObjectDumper.Write(x);
            ObjectDumper.Write(query);

            x = 200;
            ObjectDumper.Write(x);
            ObjectDumper.Write(query);
        }

        #endregion


        #region Data Manipulation

        [Category("Data Manipulation")]
        [Title("Create single Entity")]
        [Description("Create a new customer")]
        public void LinqToEntitiesData1()
        {
            Customer newCust = new Customer();

            newCust.CustomerID = "IGERB";
            newCust.ContactName = "Igvar Gerber";

            northwindContext.AddObject(newCust);
            northwindContext.SaveChanges();

            // display new Entity
            var query = northwindContext.Customers.Where(c => c.CustomerID == "IGERB");
            ObjectDumper.Write(query);

            // cleanup
            UndoLinqToEntitiesData1();
        }

        void UndoLinqToEntitiesData1()
        {
            var query = northwindContext.Customers.Where(c => c.ContactName == "Igvar Gerber");

            foreach (var res in query)
            {
                res.Delete();
            }
            northwindContext.SaveChanges();
        }

        [Category("Data Manipulation")]
        [Title("Create multiple Entities")]
        [Description("Create two new customers.")]
        public void LinqToEntitiesData2()
        {
            Customer newCust = new Customer();

            newCust.CustomerID = "IGERB";
            newCust.ContactName = "Igvar Gerber";

            northwindContext.AddObject(newCust);

            Customer newCust2 = new Customer();
            newCust2.CustomerID = "JSMIT";
            newCust2.ContactName = "John Q Smith";
            northwindContext.AddObject(newCust2);
            northwindContext.SaveChanges();

            // display new Entity
            var query = northwindContext.Customers.Where(c => c.CustomerID == "IGERB" || c.CustomerID == "JSMIT");
            ObjectDumper.Write(query);

            // cleanup
            UndoLinqToEntitiesData2();
        }

        void UndoLinqToEntitiesData2()
        {
            var query = northwindContext.Customers.Where(c => c.ContactName == "Igvar Gerber" || c.ContactName == "John Q Smith");

            foreach (var res in query)
            {
                res.Delete();
            }
            northwindContext.SaveChanges();
        }

        [Category("Data Manipulation")]
        [Title("Create an Entity with multiple tables - 1")]
        [Description("Create a new Employee Contact, which uses TPH inheritance")]
        public void LinqToEntitiesData3()
        {
            EmployeeContactSplit newEmp = new EmployeeContactSplit();

            // key will be automatically generated
            newEmp.ContactName = "Peter Gibbons";
            northwindContext.AddObject(newEmp);

            // adds rows to both ContactNameSplit and EmployeeSplit tables
            northwindContext.SaveChanges();

            // display new Entity
            var query = northwindContext.ContactsSplit.Where(c => c.ContactName == "Peter Gibbons");
            ObjectDumper.Write(query);

            // cleanup
            UndoLinqToEntitiesData3();
        }

        void UndoLinqToEntitiesData3()
        {
            var query = northwindContext.ContactsSplit.Where(e => e.ContactName == "Peter Gibbons");

            foreach (var res in query)
            {
                res.Delete();
            }
            northwindContext.SaveChanges();
        }

        [Category("Data Manipulation")]
        [Title("Create an Entity with multiple tables - 2")]
        [Description("Create a new ActiveProductFederated, which uses TPC inheritance")]
        public void LinqToEntitiesData4()
        {
            ActiveProductFedarated newProd = new ActiveProductFedarated();

            newProd.ProductName = "Digital Watch";

            northwindContext.AddObject(newProd);

            
            northwindContext.SaveChanges();

            // display new Entity
            var query = northwindContext.ProductsFedarated.Where(p => p.ProductName == "Digital Watch");
            ObjectDumper.Write(query);

            // cleanup
            UndoLinqToEntitiesData4();
        }

        void UndoLinqToEntitiesData4()
        {
            var query = northwindContext.ProductsFedarated.Where(p => p.ProductName == "Digital Watch");

            foreach (var res in query)
            {
                res.Delete();
            }
            northwindContext.SaveChanges();
        }


        [Category("Data Manipulation")]
        [Title("Create an Entity with assoicated Entities")]
        [Description("Create a new Order with references to other entities")]
        public void LinqToEntitiesData5()
        {
            //--- get the customer that we will add an order to
            Customer customer = northwindContext.Customers.Where(c => c.CustomerID == "ALFKI").AsEnumerable().First();

            //--- get the employee that we will associate with this order
            Employee employee = northwindContext.Employees.Where(e => e.EmployeeID == 1).AsEnumerable().First();

            //---- create a new order 
            Order order1 = new Order();
            order1.Customer = customer;
            order1.Employee = employee;
            order1.Freight = 21;
            order1.OrderDate = DateTime.Now;
            order1.RequiredDate = DateTime.Today.AddDays(10);
            order1.ShipAddress = customer.Address;
            order1.ShipCity = customer.City;
            order1.ShipCountry = customer.Country;
            order1.ShipPostalCode = customer.PostalCode;
            order1.ShipRegion = customer.Region;
            order1.ShipName = customer.CompanyName + "order";
            order1.Shipper = northwindContext.Shippers.AsEnumerable().First();
            northwindContext.SaveChanges(); //--- this is only needed as we are doing a common value relationship between order and order_detail for this CTP
            //----- add a product

            Product product = northwindContext.Products.AsEnumerable().First();
            order1.AddOrderDetail(product.ProductID, (decimal)product.UnitPrice, 5, 0);

            //----- save the order details instances
            northwindContext.SaveChanges();

            // display new Entity
            var query = northwindContext.Orders.Where(o => o.Customer.CustomerID == "ALFKI" && o.Freight == 21);
            ObjectDumper.Write(query);

            // cleanup
            UndoLinqToEntitiesData5();
        }

        void UndoLinqToEntitiesData5()
        {

            var query = northwindContext.Orders.Where(o => o.Customer.CustomerID == "ALFKI" && o.Freight == 21);

            List<int> orderID = query.Select(o => o.OrderID).ToList();

            foreach (int i in orderID)
            {
                var q2 = northwindContext.OrderDetails.Where(od => od.OrderID == i);
                foreach (var od in q2)
                {
                    od.Delete();
                }
            }
            northwindContext.SaveChanges();

            foreach (var res in query)
            {
                res.Delete();
            }
            northwindContext.SaveChanges();
        }

        [Category("Data Manipulation")]
        [Title("Update a single Entity")]
        [Description("Update a single Customer")]
        public void LinqToEntitiesData6()
        {
            Customer customer = northwindContext.Customers.Where(c => c.CustomerID == "ALFKI").AsEnumerable().First();

            // change the customer title
            customer.ContactTitle = "Sales Manager";

            northwindContext.SaveChanges();

            // display new Entity
            var query = northwindContext.Customers.Where(c => c.CustomerID == "ALFKI");
            ObjectDumper.Write(query);

            // cleanup
            UndoLinqToEntitiesData6();
        }

        void UndoLinqToEntitiesData6()
        {
            Customer customer = northwindContext.Customers.Where(c => c.CustomerID == "ALFKI").AsEnumerable().First();
            customer.ContactTitle = "Sales Representative";
            northwindContext.SaveChanges();
        }

        [Category("Data Manipulation")]
        [Title("Update a related single Entity")]
        [Description("Update a single order on a Customer")]
        public void LinqToEntitiesData7()
        {
            var orderquery = northwindContext
                .Customers
                .Where(c => c.CustomerID == "ALFKI")
                .SelectMany(c => c.Orders
                                .OrderBy(o => o.OrderDate)
                                .Select(o => o));

            Order order1 = orderquery.AsEnumerable().First();

            order1.Freight = 4;
            
            northwindContext.SaveChanges();

            // display new Entity
            var query = northwindContext.Orders.Where(o => o.Customer.CustomerID == "ALFKI");
            ObjectDumper.Write(query);

            // cleanup
            UndoLinqToEntitiesData7();
        }

        void UndoLinqToEntitiesData7()
        {
            var orderquery = northwindContext
                .Customers
                .Where(c => c.CustomerID == "ALFKI")
                .SelectMany(c => c.Orders
                                .OrderBy(o => o.OrderDate)
                                .Select(o => o));

            Order order1 = orderquery.AsEnumerable().First();

            order1.Freight = (decimal?)1.21;

            northwindContext.SaveChanges();
        }

        [Category("Data Manipulation")]
        [Title("Update the end of a one to many relationship")]
        [Description("Change the category which a product is a member of, show this by updating the product's category reference in the first case and adding to the category's 'products' collection in the second case")]
        public void LinqToEntitiesData8()
        {
            //--- create a new category and add it to the object context:
            Category newCategory = new Category();
            newCategory.CategoryName = "New Beverages";
            northwindContext.AddObject(newCategory);
            //--- retrieve the chai product show what category it is a member of then set the category to the new category
            Product chai = northwindContext.Products.Where(p => p.ProductName == "chai").AsEnumerable().First();
            chai.CategoryReference.Load(); //--- load the category that the product is a member of first so we can print it out
            Console.WriteLine("Chai product is a member of the '{0}' category", chai.Category.CategoryName);
            chai.Category = newCategory;
            northwindContext.SaveChanges();

            Console.WriteLine("Chai product is now a member of the '{0}' category", chai.Category.CategoryName);

            //---- put the category back into the beverages category 
            Category beverages = northwindContext.Categories.Where(c => c.CategoryName == "Beverages").AsEnumerable().First();
            beverages.Products.Add(chai);

            Console.WriteLine("Chai product is now a member of the '{0}' category", chai.Category.CategoryName);
            northwindContext.SaveChanges();
        }

        [Category("Data Manipulation")]
        [Title("Update the end of a many to many relationship")]
        [Description("Add a new territory to an employee")]
        public void LinqToEntitiesData9()
        {
            //--- retrieve an employee
            Employee employee = northwindContext.Employees.Where(e => e.EmployeeID == 1).AsEnumerable().First(); ;
            employee.Territories.Load(); //--- load the territories
            Console.WriteLine("Employee has these territories...");
            foreach (Territory territory in employee.Territories)
            {
                Console.WriteLine("   - {0}", territory.TerritoryDescription);
            }

            //--- retrieve a territory
            Territory providence = northwindContext.Territories.Where(t => t.TerritoryID == "02903").AsEnumerable().First();
            employee.Territories.Add(providence);
            northwindContext.SaveChanges();

            ////--- dump updated territories
            Console.WriteLine("Employee has these territories now...");
            foreach (Territory territory in employee.Territories)
            {
                Console.WriteLine("   - {0}", territory.TerritoryDescription);
            }

            //--- set back to original state
            providence.Employees.Remove(employee);
            northwindContext.SaveChanges();

            //--- dump updated territories
            Console.WriteLine("Employee has these territories now...");
            foreach (Territory territory in employee.Territories)
            {
                Console.WriteLine("   - {0}", territory.TerritoryDescription);
            }
        }

        [Category("Data Manipulation")]
        [Title("Edit the address of a split entity")]
        [Description("Change the address in a contactsplit entity where the address is in one of many tables that make up the entity")]
        public void LinqToEntitiesData11()
        {
            //--- retrieve the employee
            EmployeeContactSplit employee = (EmployeeContactSplit)northwindContext.ContactsSplit.Where(c => c.ContactID == 121).AsEnumerable().First();
            Console.WriteLine("Employee address = {0}", employee.Address);

            //--- change the address to something else
            employee.Address = "231 55th pl";
            northwindContext.SaveChanges();
            Console.WriteLine("Updated Employee address = {0}", employee.Address);

            //--- change it back
            employee.Address = "507 - 20th Ave. E.  Apt. 2A";
            northwindContext.SaveChanges();
            Console.WriteLine("Updated Employee address = {0}", employee.Address);
        }

        [Category("Data Manipulation")]
        [Title("Edit a federated entity")]
        [Description("Change the address in a contactsplit entity where the address is in one of many tables that make up the entity")]
        public void LinqToEntitiesData12()
        {
            //--- retrieve the prodcuct
            ProductFedarated product = northwindContext.ProductsFedarated.Where(p => p.ProductID == 16).AsEnumerable().First();
            Console.WriteLine("Product name = {0}", product.ProductName);
            product.ProductName += "2";
            northwindContext.SaveChanges();
            Console.WriteLine("Product name = {0}", product.ProductName);
        }

        [Category("Data Manipulation")]
        [Title("Delete single Entity")]
        [Description("")]
        public void LinqToEntitiesData13()
        {
            Employee newEmp = new Employee();

            // key will be automatically generated
            newEmp.FirstName = "Peter";
            newEmp.LastName = "Gibbons";
            northwindContext.AddObject(newEmp);

            // adds rows to both ContactNameSplit and EmployeeSplit tables
            northwindContext.SaveChanges();

            // get the new key
            int id = newEmp.EmployeeID;

            // display new Entity
            var query = northwindContext.Employees.Where(e => e.EmployeeID == id);
            ObjectDumper.Write(query);

            // cleanup
            newEmp.Delete();
            northwindContext.SaveChanges();
        }

        [Category("Data Manipulation")]
        [Title("Optimistic concurrency")]
        [Description("Create a situation where we update a value that has already been updated. Fail with a concurrency violation, fix up by doing a refresh from the store and a re-save")]
        public void LinqToEntitiesData14()
        {
            //--- create two contexts
            EntityNorthwindContext northwind1 = new EntityNorthwindContext(connstr);
            EntityNorthwindContext northwind2 = new EntityNorthwindContext(connstr);

            //--- retrieve the same product instance from both contexts
            ProductFedarated pf1 = northwind1.ProductsFedarated.First();
            ProductFedarated pf2 = northwind2.ProductsFedarated.First();

            //--- dump the ID and names
            Console.WriteLine("Product from context 1: ID={0}, Name={1}; Product from context 2: ID={2}, Name={3}", pf1.ProductID, pf1.ProductName, pf2.ProductID, pf2.ProductName);

            //--- change and save pf1
            pf1.ProductName += "2";
            northwind1.SaveChanges();
            Console.WriteLine("---- pf1 saved succesfully -----------");

            //--- change and save pf2
            try
            {
                pf2.ProductName += "Chai";
                northwind2.SaveChanges();
            }
            catch (System.Data.OptimisticConcurrencyException oce)
            {
                Console.WriteLine("---- concurrency violation caught ----------");
                Console.WriteLine(oce.Message);
            }

            //--- refresh and save pf2
            northwind2.Refresh(System.Data.Objects.RefreshMode.StoreWins, pf2);
            Console.WriteLine("product from context 2: ID={0}, name={1}", pf2.ProductID, pf2.ProductName);

            //--- make the change again and save
            pf2.ProductName = "Chai";
            northwind2.SaveChanges();
            Console.WriteLine("---- pf2 saved succesfully -----------");

        }

        #endregion

        #region Object Context

        [Category("Object Context and Metadata")]
        [Title("Loading and State Management 1")]
        [Description("This sample loads Entities without state management")]
        public void LinqToEntitiesOC1()
        {
            //--- execute a query for some instances without management in the state manager:
            IEnumerable<Category> categories = northwindContext.Categories.Execute(MergeOption.NoTracking);
            //--- print the category and the entity state : the state will be detached which indicates that it is not being managed in the state manager
            foreach (Category category in categories)
            {
                Console.WriteLine("Category Name: {0}; \n      - State Manager Reference:{1}", category.CategoryName, category.EntityState);
            }
        }

        [Category("Object Context and Metadata")]
        [Title("Loading and State Management 2")]
        [Description("This sample loads Entities with AppendOnly")]
        public void LinqToEntitiesOC2()
        {
            //--- execute a query for some instances append them to the state manager
            IEnumerable<Category> categories = northwindContext.Categories.Execute(MergeOption.AppendOnly);
            //--- print the category and the entity state : the state will be unchanged which indicates that the instances are in the state manager but not changed
            foreach (Category category in categories)
            {
                Console.WriteLine("Category Name: {0}; \n      - State Manager Reference:{1}", category.CategoryName, category.EntityState);
            }
        }

        [Category("Object Context and Metadata")]
        [Title("Loading and State Management 3")]
        [Description("This sample loads Entities overwriting in-memory edits")]
        public void LinqToEntitiesOC3()
        {
            //--- get a category to change
            Category cat = northwindContext.Categories.AsEnumerable().First();
            cat.CategoryName = "wwwww";
            Console.WriteLine("Category changed to have the name: {0} with ID: {1} \n", cat.CategoryName, cat.CategoryID);

            //--- execute a query for some instances append them to the state manager
            Console.WriteLine("retrieving categories and overwriting any changes in the state manager...\n");
            IEnumerable<Category> categories = northwindContext.Categories.Execute(MergeOption.OverwriteChanges);
            //--- print the category and the entity state : the state will be unchanged which indicates that the instances are in the state manager but not changed
            foreach (Category category in categories)
            {
                Console.WriteLine("Category ID: {0}, CategoryName: {1}, Entity State:{2}", category.CategoryID, category.CategoryName, category.EntityState);
            }
        }

        [Category("Object Context and Metadata")]
        [Title("Loading and State Management 4")]
        [Description("This sample loads Entities saving in-memory edits")]
        public void LinqToEntitiesOC4()
        {
            //--- get a category to change
            Category cat = northwindContext.Categories.AsEnumerable().First();
            cat.CategoryName = "wwwww";
            Console.WriteLine("Category changed to have the name: {0} with ID: {1} \n", cat.CategoryName, cat.CategoryID);

            //--- execute a query for some instances append them to the state manager
            Console.WriteLine("retrieving categories without overwriting any changes in the state manager...\n");
            IEnumerable<Category> categories = northwindContext.Categories.Execute(MergeOption.PreserveChanges);
            //--- print the category and the entity state : note that the changed entity remains changed
            foreach (Category category in categories)
            {
                Console.WriteLine("Category ID: {0}, CategoryName: {1}, Entity State:{2}", category.CategoryID, category.CategoryName, category.EntityState);
            }
            //--- note we are not invoking save changes so even though the change remains in-memory we wont push this to the store
            //--- calling save changes would push the change to the store but we want to leave the store in its original state
        }

        /*
        [Category("Object Context and Metadata")]
        [Title("EDM Metadata Example - Generate queries dynamically using the EDM metadata")]
        [Description("Enumerate through all entitysets and generate a basic query for each, use EDM metadata to dump the results of the query.")]
        public void LinqToEntitiesOC5()
        {
            //---- get the conceptual entity container
          
            EntityContainer container = northwindContext.MetadataWorkspace.GetEntityContainer("EntityNorthwindContext", MetadataWorkspace.CSpace);
            //---- dereference the entity connection and open it
            EntityConnection conn = (EntityConnection)northwindContext.Connection;
            conn.Open();
            //---- iterate through the base entity sets, find the concrete entitysets in the collection and
            //---- create a basic query using the eset information
            foreach (EntitySetBase eset in container.BaseEntitySets)
            {
                if (eset is EntitySet)//--- could be a relationship set, we just want entity sets
                {
                    Console.WriteLine("\n\n--- query entity set {0} ---\n\n", eset.Name);
                    //--- create the query dynamically
                    String query = String.Format("Select value e from {0}.{1} as e", eset.EntityContainer.Name, eset.Name);
                    //--- execute an entityclient command and get the results
                    EntityCommand command = conn.CreateCommand();
                    conn.Open();
                    command.CommandText = query;
                    DbDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
                    //--- dump the information
                    while (reader.Read())
                    {
                        IExtendedDataRecord iedr = (IExtendedDataRecord)reader;
                        EntityType entityType = (EntityType)iedr.DataRecordInfo.Metadata.EdmType;
                        Console.WriteLine("---------------------------------------------------------------------------");
                        Console.WriteLine("Type : {0}", entityType.FullName);
                        foreach (EdmProperty property in entityType.Properties)
                        {
                            Console.WriteLine("   property: {0} = {1}  ", property.Name, reader[property.Name]);
                        }
                    }
                }
            }
        }
        */
        #endregion



    }
}

